<?php

namespace app\admin\controller;

use app\common\controller\BasicAdmin;
use app\common\model\CourseAttendanceModel;
use app\common\model\SchoolClassModel;
use app\common\model\SchoolGradeModel;
use app\common\model\UserModel;
use app\common\model\UserTimesModel;
use app\common\traits\SelectPage;
use app\common\model\CourseModel;
use app\common\model\SchoolModel;
use library\tools\Data;
use think\Db;
use think\Exception;

class Student extends BasicAdmin
{
    use SelectPage;
    public $model = 'UserModel';

    /**
     * 学生列表
     */
    public function index()
    {
        $query = $this->_query($this->model)
            ->join('edu_user_times t','t.user_id=edu_user.id','LEFT')
            ->join('edu_course c','t.course_id=c.id','LEFT')
            ->join('edu_school_class clas','clas.id=edu_user.class_id','LEFT')
            ->join('edu_user u2','u2.id=edu_user.pid','LEFT')
            ->where('edu_user.role', 1)
//            ->where('edu_user.id', 'neq', 1)
//            ->where('edu_user.id', 'neq', 2)
//            ->append(['poster_paylog_total'])
            ->field('edu_user.*,u2.realname AS bind_name, GROUP_CONCAT(DISTINCT c.name) as course_name')
            ->group('edu_user.id')
//            ->fetchSql()->select()
            ;
        if(trim(input('realname'))){
            $query->where('edu_user.realname','like','%'.trim(input('realname')).'%');
        }
        if(trim(input('phone'))){
            $query->where('edu_user.phone','like','%'.trim(input('phone')).'%');
        }
        if(trim(input('school_id'))){
            $query->where('edu_user.school_id','=',trim(input('school_id')));
        }
        if(trim(input('grade_id'))){
            $query->where('edu_user.grade_id','=',trim(input('grade_id')));
        }
        if(trim(input('class_id'))){
            $query->where('edu_user.class_id','=',trim(input('class_id')));
        }
        return $query->page();
    }

    /**
     * 详情
     */
    public function detail()
    {
        $user_id = $this->request->get('id');
        $user =  UserModel::where('id',$user_id)->with(['school','schoolgrade','schoolclass'])->find();
        //常规
        if($user['school_id']){
            $user['normal'] = CourseModel::where('status',1)
                ->where('type',1)
                ->where('school_id',$user['school_id'])
                ->where('grade_id='.$user['grade_id'].' or grade_id is null')
                ->where('class_id='.$user['class_id'].' or class_id is null')
                ->select();
        }else{
            $user['normal'] = [];
        }

        //兴趣
        $course_ids = UserTimesModel::where('user_id',$user['id'])
            ->field('group_concat(course_id) as course_ids')->find()['course_ids'];
        $user['interest'] = CourseModel::where('status',1)
            ->where('type',2)
            ->where('id','in',$course_ids)
            ->select();
        //考勤
        $attendance = CourseAttendanceModel::where('find_in_set(in_userids,'.$user_id.')')->select();
        foreach ($attendance as $key=>$value){
            $value['course_name'] = CourseModel::where('id',$value['course_id'])->value('name');
        }
        $user['attendance'] = $attendance;
        //获取学生的学习时长与次数
        $user['count'] = CourseAttendanceModel::where('find_in_set('.$user['id'].',in_userids)')->count();
        $user['time'] =  ceil(45*$user['count']/60);
        $this->assign('user',$user);
        $this->fetch();
    }

    /**
     * 设置真实姓名
     */
    public function set_realname()
    {
        $this->title = '编辑';
        return $this->_form($this->model, 'realname');
    }

    public function set_bind_student(){
        return $this->_form($this->model, 'bind_student');
    }

    public function export_student_form()
    {
        return $this->_form($this->model, 'export_form');
    }

    /**
     * 导出学生数据
     */
    public function export_student()
    {
        $normalCourseIds = [];
        //常规课-搜索常规课必须学校id
        if($this->request->has('school_id','get') && !empty($this->request->get('school_id')))
        {
            $normalCourseIds = CourseModel::where(function ($db){
                $db->where('status',1);
                $db->where('type',1);
                $db->where('school_id',$this->request->get('school_id'));

                $grade_id = $this->request->get('grade_id');
                if($grade_id){
                    $db->where('grade_id='.$grade_id.' or grade_id is null');
                }
                $class_id = $this->request->get('class_id');
                if($class_id){
                    $db->where('class_id='.$class_id.' or class_id is null');
                }

            })->column('id');
        }

        $query =  UserModel::join('edu_user_times t','t.user_id=edu_user.id','LEFT')
        ->join('edu_course c','t.course_id=c.id','LEFT')
        ->join('edu_school_class clas','clas.id=edu_user.class_id','LEFT')
        ->join('edu_school_grade grade','grade.id=edu_user.grade_id','LEFT')
        ->join('edu_user u2','u2.id=edu_user.pid','LEFT')
        ->where('edu_user.role', 1)
        ->field('edu_user.*,u2.realname AS bind_name, GROUP_CONCAT(DISTINCT c.name) as course_name,grade.name as grade_name, clas.name as class_name')
        ->group('edu_user.id');

        if(trim(input('school_id'))){
            $query->where('edu_user.school_id','=',trim(input('school_id')));
        }
        if(trim(input('grade_id'))){
            $query->where('edu_user.grade_id','=',trim(input('grade_id')));
        }
        if(trim(input('class_id'))){
            $query->where('edu_user.class_id','=',trim(input('class_id')));
        }
        if(trim(input('course_id'))){
            $query->where('c.id','=',trim(input('course_id')));
        }

        $list = $query->select();

        foreach ($list as $key => $item)
        {
            $list[$key]['xuhao'] = $key;
        }

        $table_field = [
            ['A' ,'xuhao', '序号'],
            ['B' ,'realname','学生姓名'],
            ['C' ,'student_no', '学籍号'],
            ['D' ,'school_name','学校'],
            ['E' ,'course_name','课程名称'],
            ['F' ,'grade_name','年级'],
            ['G' ,'class_name','班级'],
        ];

        return $this->_export($list, $table_field, "学生数据");
    }

    /**
     * 导入学生数据
     */
    public function import_student()
    {
        require(dirname(__FILE__) . '/../../../vendor/spreadsheet/php-excel-reader/excel_reader2.php');
        require(dirname(__FILE__) . '/../../../vendor/spreadsheet/SpreadsheetReader.php');
        $file = $this->request->file('file');
        if (empty($file)) {
            $this->error('未上传文件');
        }else {
            $upload = array(
                'uploadurl' => 'ajax/upload',
                'cdnurl'    => '',
                'savekey'   => '/upload/excel/{filemd5}{.suffix}',
                'maxsize'   => '1024mb',
                'mimetype'  => 'xls,xlsx',
                'multiple'  => false
            );

            preg_match('/(\d+)(\w+)/', $upload['maxsize'], $matches);
            $type = strtolower($matches[2]);
            $typeDict = ['b' => 0, 'k' => 1, 'kb' => 1, 'm' => 2, 'mb' => 2, 'gb' => 3, 'g' => 3];
            $size = (int)$upload['maxsize'] * pow(999999, isset($typeDict[$type]) ? $typeDict[$type] : 0);
            $fileInfo = $file->getInfo();
            $suffix = strtolower(pathinfo($fileInfo['name'], PATHINFO_EXTENSION));
            $suffix = $suffix ? $suffix : 'file';

            $mimetypeArr = explode(',', strtolower($upload['mimetype']));
            $typeArr = explode('/', $fileInfo['type']);

            //验证文件后缀
            if ($upload['mimetype'] !== '*' &&
                (
                    !in_array($suffix, $mimetypeArr)
                    || (stripos($typeArr[0] . '/', $upload['mimetype']) !== false && (!in_array($fileInfo['type'], $mimetypeArr) && !in_array($typeArr[0] . '/*', $mimetypeArr)))
                )
            ) {
                $this->error('只能上传xls,xlsx文件');
            }
            $replaceArr = [
                '{year}'     => date("Y"),
                '{mon}'      => date("m"),
                '{day}'      => date("d"),
                '{hour}'     => date("H"),
                '{min}'      => date("i"),
                '{sec}'      => date("s"),
                '{filename}' => $suffix ? substr($fileInfo['name'], 0, strripos($fileInfo['name'], '.')) : $fileInfo['name'],
                '{suffix}'   => $suffix,
                '{.suffix}'  => $suffix ? '.' . $suffix : '',
                '{filemd5}'  => md5_file($fileInfo['tmp_name']),
            ];
            $savekey = $upload['savekey'];
            $savekey = str_replace(array_keys($replaceArr), array_values($replaceArr), $savekey);

            $uploadDir = substr($savekey, 0, strripos($savekey, '/') + 1);
            $fileName = substr($savekey, strripos($savekey, '/') + 1);
            $splInfo = $file->move( config('app.file_storage_path') . $uploadDir, $fileName);
            $file = config('app.file_storage_path') . $uploadDir. $fileName;
            $Reader = new \SpreadsheetReader($file);
            // 启动事务
            Db::startTrans();
            try {
                foreach ($Reader as $Key => $Row) {
                    if ($Key > 0 && !empty($Row)) {
                        $name = $Row[1];
                        if ($name != '' and !empty($Row[2]) and !empty($Row[3])) { // 姓名学校学籍号必填
                            //找到分类id
                            $student_no = isset($Row[1])?$Row[1]:'';
                            $realname = isset($Row[2])?$Row[2]:'';
                            $school_name = isset($Row[3])?$Row[3]:'';
                            $school_id = SchoolModel::where('name',trim($Row[3]))->value('id');
                            $grade_id = SchoolGradeModel::where('name',trim($Row[4]))->value('id');
                            $class_id = SchoolClassModel::where('name',trim($Row[5]))->value('id');

                            // 过滤学号重复
                            $stu = UserModel::where('student_no', trim($student_no))->find();
                            if(!empty($stu)){
                                continue;
                            }


                                $user = UserModel::create([
                                    'realname'=>trim($realname),
                                    'openid'=>md5(time().rand(100, 999)),
                                    'role'=>'1',
                                    'school_id'=>$school_id,
                                    'school_name'=>trim($school_name),
                                    'grade_id'=>$grade_id,
                                    'class_id'=>$class_id,
                                    'student_no'=>trim($student_no),
                                    'pwd'=>md5('123456'),
                                ]);

                                if(!empty(trim($Row[6]))) {
                                    $course = CourseModel::where('name',trim($Row[6]))->find();
                                    $cui = new CourseInterest;
                                    if (!$cui->getAddUserTimeLock($course['id'])){
                                        $this->error('服务器拥挤，请重试','',400);
                                    }
                                    $ret = $cui->add_user_time_list($user->id,$course['id'],$course);
                                    if ($ret !== true){
                                        throw new Exception($ret,400);
                                    }
                                    $this->updateAddUserTimeLock($course['id']);  //续锁时间
                                }
                        }
                    }
                }
                // 提交事务
                Db::commit();
            }catch (\Exception $exception){
                // 回滚事务
                Db::rollback();
                $msg = $exception->getMessage() || '上传失败，格式错误';
                $this->error($msg);
            }
            $this->success('导入成功，课程已更新！');
        }
    }

    /**
     * 导入模板
     */
    public function import_student_template()
    {
        $list[0] = [
            'num'=>'示例请删除 1',
            'student_no'=>'xxxxxx',
            'realname'=>'张三',
            'school_name'=>'深圳实验学校',
            'grade_name'=>'高中一年级',
            'class_name'=>'一班',
            'course_name'=>'语文',
        ];
        $table_field = [
            ['A' ,'num', '序号'],
            ['B' ,'student_no','学籍号'],
            ['C' ,'realname','姓名'],
            ['D' ,'school_name', '学校'],
            ['E' ,'grade_name','年级'],
            ['F' ,'class_name','班级'],
            ['G' ,'course_name','课程名称'],
        ];
        return $this->_export($list, $table_field, '学生导入模板');
    }

    public function selectpage_correct()
    {
        //设置过滤方法
        $this->request->filter(['strip_tags', 'htmlspecialchars']);
        //搜索关键词,客户端输入以空格分开,这里接收为数组
        $word = (array)$this->request->request("q_word/a");
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");
        //搜索条件
        $andor = $this->request->request("andOr", "and", "strtoupper");
        //排序方式
        $order = (array)$this->request->request("orderby/a");
        //显示的字段
        $field = $this->request->request("showField");
        //主键
        $searchKey = $this->request->request("searchKey");
        //主键值
        $searchValue = $this->request->request("searchValue");
        //搜索字段
        $searchfield = $this->request->request("searchField");
        $searchfield = $searchfield ? explode(',', $searchfield) : [];
        //自定义搜索条件
        $custom = (array)$this->request->request("custom/a");


        $with = $this->request->request('with');
        $with = $with ? explode(',', $with) : [];


        $where = [
            'role'=>1,
            'pid'=>0,
        ];

        foreach (input() as $key=>$value){
            if(!empty(input($key)) && in_array($key,$this->model->fields)){
                $where[$key] = $value;
            }
        }

        $list = [];
        $total = $this->model->where($where)->count();
        if ($total > 0) {
            $list = $this->model->where($where)
                ->with($with)
                ->order($order)
                ->page($page, $pagesize)
                ->field($this->selectpageFields())
                ->fetchCollection()
                ->select()->toArray();
            foreach ($list as $index => &$item) {
                unset($item['password'], $item['salt']);
            }
        }
        //这里一定要返回有list这个字段,total是可选的,如果total<=list的数量,则会隐藏分页按钮
        $data = ['list' => $list, 'total' => $total];
        $this->success($this->model->getLastSql(), $data);
    }

}